<?php 
$req_path="./";
include_once($req_path."include/ini.php");
require_once($req_path."include/db.php");

class M_order extends DB{
	private $dbConn;
	private $rowCount="0";
	private $ID="0";
	private $sqlSelect =" select  * from tb_order_product o  Where 1=1 " ;
	private $totalpage="0";
	
	function M_order(){
		parent::__construct();
	}
	function getListPage(){
		global $list_page;
		return $list_page;
	}

	function getRunning($type='order'){
		$sql ="select value from tb_running where name='".$type."' and r_date=current_date() " ;
		$result = $this->ExecuteData($sql);
		$cnt=$result->RecordCount();
		
		if ( $cnt > 0 ) {
			$row=$result->FetchRow();
			$sqlupdate="update tb_running set value=value+1 where name='".$type."'";
			$this->ExecuteData($sqlupdate);
			return $row["value"]+1;	
		}else{
			$sqlupdate="update tb_running set value=1 , r_date=current_date() where name='".$type."'";
			$this->ExecuteData($sqlupdate);
			return 1;
		}
	}
	function getStockHO($pid){
		$sql = "SELECT MainAmt
				FROM tb_stock_product s
				JOIN tb_branch b ON b.ID = s.BranchID
				WHERE s.ProductID = ".$pid." AND b.IsActived=1 AND b.Type =1 limit 0, 1 ";
				
		$result  = $this->ExecuteData($sql);			
		$rows =$result->RecordCount();
		if ($rows > 0){
			$row=$result->FetchRow();
			return $row['MainAmt'];
		}else{
			return 0;
		}
	}
	function getData( $code=null ,$branch=0, $returndatefrom=null, $returndateto=null, $status=0 ,$pageStart=1,$IsHo=0){
		global $list_page;
		$sqlscript= "select r.ID , r.OrderNo , b.BranchName ,"
				." Case when r.Status=1 then DATE_FORMAT( r.CreatedDate, '%d/%m/%Y' ) "
				."  when r.Status=2 then DATE_FORMAT( OrderDate, '%d/%m/%Y' )  "
				."  when r.Status=3 then DATE_FORMAT( ConfirmDate, '%d/%m/%Y')  "
				."	else DATE_FORMAT( ClosedDate, '%d/%m/%Y' )  end ChangeDate ,"
				." Case when r.Status=1 then r.CreatedBy "
				."  when r.Status=2 then OrderBy"
				."  when r.Status=3 then ConfirmBy"
				."	else ClosedBy end ChangeBy ,"
				." Case when r.Status=1 then 'Created' "
				."  when r.Status=2 then 'Order' "
				."  when r.Status=3 then 'Confirm' "
				."	else 'Closed' end Status , r.Status as iStatus,"
				." Amount ,Total "
				.", Case when r.Status=1 then '0'
				  when r.Status=2 then '0'
				  when r.Status=3 then
            (SELECT count(*)
           FROM tb_order_product_detail pd where pd.OrderID=r.ID and Amount_r <> Amount_s )
					else
          (SELECT count(*)
           FROM tb_order_product_detail pd where pd.OrderID=r.ID  and Amount_r <> Amount_c   )
           end Flage "
				." from tb_order_product r "
				." left join tb_branch b on r.Branch=b.ID "
				." Where 1=1 and r.IsDeleted=0  ";
		
		if (isset($code) && $code!=""){
			$sqlscript.= " and r.OrderNo = '". $code."'";
		}
		if (isset($branch) && $branch!="0"){
			$sqlscript.= " and r.Branch = ". $branch;
		}
		if (isset($returndatefrom) && $returndatefrom!=""){
			list($day, $month, $year) = split('/', $returndatefrom);
			$Rfrom= $year."/".$month."/".$day;
			list($day, $month, $year) = split('/', $returndateto);
			$Rto= $year."/".$month."/".$day;
			
			$sqlscript.= " and r.OrderDate Between '". $Rfrom."' and '".$Rto."'";
		}
		if (isset($status) && $status!=0){
			$sqlscript.= " and r.Status = ".$status;
		}
				
		if ($IsHo==1){
			/// When branch create return product don't show
			$sqlscript.= " and r.Status  <> 1 ";
		}
		//Order 
		$sqlscript.= " order by  CASE WHEN r.Status = 1 THEN r.CreatedDate WHEN r.Status = 2 THEN r.OrderDate WHEN r.Status = 3  THEN r.ConfirmDate ELSE  r.ClosedDate END  Desc " ;
		//Execute
		$result = $this->ExecuteData($sqlscript);
		//Get all rows
		$rows =$result->RecordCount();
	
		//Page limit
		$sqlscript.= " limit ". ($pageStart-1)*$list_page. ", " . $list_page;
		//paging
		$result = $this->ExecuteData($sqlscript);		
	
		$totalpage=1;
		if ($rows>0){
			$rt = $rows%$list_page;
			
			if($rt!=0) {
				$totalpage = floor($rows/$list_page)+1;
			}
			else {
				$totalpage = floor($rows/$list_page);
			}
		}
		
		$this->rowCount=$rows;
		$this->totalpage=$totalpage;	
		
		return $result;
	}
	
	function getProductDetail($id=0, $amt=0){
		$sqlscript= " Select p.ID as ProductID , ProductName , u.Unit, Price ,".$amt ." as Amount ";
		$sqlscript.= " from tb_product p ";
		$sqlscript.= " join tb_units u on p.MainUnit=u.ID  "
					. " where IsDeleted=0 ";
		$sqlscript.= " and p.ID='". $id."'";
		$result = $this->ExecuteData($sqlscript);
		return $result->FetchRow();
	}

	
	function getViewDataDetail($id){
			$sql = 'SELECT re.ID, p.ID AS ProductID, p.ProductName, pt.Type as ProductType, c.CatName, u.Unit'
        . ' , re.Amount_s , re.Amount_r, re.Amount_c '
		. ' , case when op.Status=1 or op.Status=2 then re.Amount_s  when op.Status=3 then re.Amount_r else re.Amount_c end Amount'
		. ' FROM tb_order_product_detail re'
		. ' JOIN tb_order_product op on op.ID=re.OrderID '
        . ' LEFT JOIN tb_product p ON re.Product = p.ID'
        . ' LEFT JOIN tb_product_type pt ON p.Type = pt.ID'
        . ' LEFT JOIN tb_product_category c on c.CatID=p.Category'
        . ' LEFT Join tb_units u on u.ID=p.MainUnit'
        . ' WHERE 1=1 and op.IsDeleted=0 '
        . ' AND re.OrderID ='.$id
        . ' Order by p.ProductName  ';
		$result2 = $this->ExecuteData($sql);
		return $result2 ;
	}
	////////////////////////////////////////
	function getDataByID($id){
		$sqlscript= "select r.ID , r.OrderNo , b.BranchName ,"
				." Case when r.Status=1 then DATE_FORMAT( r.CreatedDate, '%d/%m/%Y' ) "
				."  when r.Status=2 then DATE_FORMAT( OrderDate, '%d/%m/%Y' )  "
				."  when r.Status=3 then DATE_FORMAT( ConfirmDate, '%d/%m/%Y')  "
				."	else DATE_FORMAT( ClosedDate, '%d/%m/%Y' )  end ChangeDate ,"
				." Case when r.Status=1 then r.CreatedBy "
				."  when r.Status=2 then OrderBy"
				."  when r.Status=3 then ConfirmBy"
				."	else ClosedBy end ChangeBy ,"
				." Case when r.Status=1 then 'Created' "
				."  when r.Status=2 then 'Order' "
				."  when r.Status=3 then 'Confirm' "
				."	else 'Closed' end Status ,"
				." Amount ,Total , r.Status as iStatus"
				." from tb_order_product r "
				." left join tb_branch b on r.Branch=b.ID "
				." Where 1=1 and r.IsDeleted=0 and r.ID=".$id;
		$result = $this->ExecuteData($sqlscript);		
		return $result->FetchRow();
	}
	//for view data
	function ViewHeader($id){
		$sqlscript= "select r.ID , r.OrderNo, r.Branch , b.BranchName,r.Status as iStatus " 
				." , coalesce(r.OrderBy,'-') as OrderBy "
				." , coalesce(r.CreatedBy,'-') as CreatedBy "
				." , coalesce(r.ConfirmBy,'-') as ConfirmBy "				
				." , coalesce(r.ClosedBy,'-') as ClosedBy "				
				." , coalesce(DATE_FORMAT( r.OrderDate, '%d/%m/%Y' ),'-') as OrderDate "
				." , coalesce(DATE_FORMAT( r.ConfirmDate, '%d/%m/%Y' ),'-') as ConfirmDate"
				." , coalesce(DATE_FORMAT( r.CreatedDate, '%d/%m/%Y' ),'-') as CreatedDate  "
				." , coalesce(DATE_FORMAT( r.ClosedDate, '%d/%m/%Y' ),'-') as ClosedDate  "
				." , Case when r.Status=1 then 'Created' "
				."  when r.Status=2 then 'Order' "
				."  when r.Status=3 then 'Confirm' "
				."	else 'Closed' end Status "
				." from tb_order_product r "
				." left join tb_branch b on r.Branch=b.ID Where 1=1"
				." and r.ID=".$id 
				." and r.IsDeleted=0 ";
				
		$result = $this->ExecuteData($sqlscript);		
		return $result->FetchRow();
	}
	// View  detail
	function ViewDetail($id,$pageStart=1,$mode='d'){
		global $list_page;
		 
		$sqlBranch = 'select id from tb_branch b where b.Type=1 and IsActived=1' ;
		$result = $this->ExecuteData($sqlBranch);
		$rBranch = $result->FetchRow();
		
		$sql = 'SELECT re.ID, p.ID AS ProductID, p.ProductName, pt.Type as ProductType, c.CatName, u.Unit'
        . ' , re.Amount_s,re.Amount_c , re.Amount_r , s.MainAmt as RemainAmt ,s1.MainAmt as RemainHOAmt ,  s1.Reserv_Amt '
		. ',  ( SELECT SUM(Amount_s) ResvrAmt
				FROM tb_order_product_detail d2
				JOIN tb_order_product o2 ON o2.id = d2.OrderID
				WHERE o2.IsDeleted = 0 AND d2.Product = p.ID AND o2.`Status` = 2)  RAmt  '
        . ' FROM tb_order_product_detail re'
		. ' JOIN tb_order_product o on o.ID = re.OrderID'
        . ' JOIN tb_product p ON re.Product = p.ID'
        . ' LEFT JOIN tb_product_type pt ON p.Type = pt.ID'
        . ' LEFT JOIN tb_product_category c on c.CatID=p.Category'
		. ' LEFT JOIN tb_stock_product s on s.ProductID = re.Product and s.BranchID = o.Branch '
		. ' LEFT JOIN tb_stock_product s1 on s1.ProductID = re.Product and s1.BranchID='.$rBranch['id']
        . ' LEFT Join tb_units u on u.ID=p.MainUnit'
        . ' WHERE o.IsDeleted=0 and 1=1'
        . ' AND re.OrderID ='.$id
        . ' Order by p.ProductName  ';

		//Execute
	//	print_r($sql);
		$result = $this->ExecuteData($sql);
		//Get all rows
		$rows =$result->RecordCount();		
		//Page limit
		//$sql.= " limit ". ($pageStart-1)*$list_page. ", " . $list_page;
		//paging
		//return $sql;
		// 'd' => detail mode 
		if ($mode=='d'){
			$result = $this->ExecuteData($sql);		
		}
	
		$totalpage=1;
		if ($rows>0){
			$rt = $rows%$list_page;
			
			if($rt!=0) {
				$totalpage = floor($rows/$list_page)+1;
			}
			else {
				$totalpage = floor($rows/$list_page);
			}
		}
		
		$this->rowCount=$rows;
		$this->totalpage=$totalpage;	
		
		return $result;		
	}
	//End  view data

	function Excute($sql){
		$this->ExecuteData($sql);
	}
	function getRowCount(){
		return $this->rowCount;
	}
	
	function getLastInsert(){
		return $this->ID;
	}
	function getUserOrderBranch($oid){
		$sql="select u.Telephone, u.Email  "
			." from tb_order_product o "
			." left join tb_user u on u.UserID=o.UpdatedUserID "
			." where o.IsDeleted=0 and o.ID=".$oid;
		$result=$this->ExecuteData($sql);
		$count=$result->RecordCount();
		$rows = $result->FetchRow();
		
			$ar=array("Tele"=>$rows["Telephone"]
				,"Email"=>$rows["Email"]
				,"Count"=>$count
				);
	 
		return $ar;
	}
	
	function getUserAdmin_Tele_Email(){
		$sql="select u.Telephone, u.Email "
		." from tb_user u "
		." join tb_branch b on b.ID=u.BranchID "
		." where u.SMS='1' and u.IsActived=1 and b.Type=1 ";
		
		$result=$this->ExecuteData($sql);
		$count= $result->RecordCount();
		$tel="";
		$email="";
		while($rows = $result->FetchRow()){
			$tel .= ",".$rows["Telephone"];
			$email.=",".$rows["Email"];
		}
		$tel = substr($tel,1);
		$email = substr($email,1);
		
		$ar=array("Tele"=>$tel
				,"Email"=>$email
				,"Count"=>$count
				);
		return $ar;
	}
	
	function InsertData($val){
		if (isset($val)){
			$sqlInsert=$this->InsertSqlScript($this->sqlSelect, $val);
			$this->ExecuteData($sqlInsert);
			$this->ID = $this->getLastID();
			return $this->ID;
		}
	}
	function updateData($sql, $val){
		if(isset($val)){
			$sqlUpdate=$this->UpdateSqlScript($sql,$val);
			return $this->ExecuteData($sqlUpdate);
		}
	}
	function delData($id ,$user){
		if(isset($id)){
			$sqlDelete = "Update tb_order_product set IsDeleted=1 ,UpdatedDate = NOW(), UpdatedBy='".$user."' where ID='".$id."'";
			$this->ExecuteData($sqlDelete);
			//delete detail
			//$this->delDataDetail($id);
			return "c";
		}
	}
	 
	function delDataDetail($id){
			$sqlDelete = "delete from tb_order_product_detail where OrderID='".$id."'";
			$this->ExecuteData($sqlDelete);
	}
	function getTotalPage(){
		return $this->totalpage;
	}
	function getDataCBO($t="b"){
		if ($t=="b")
			$sqlscript= "select ID as ID, BranchName as Name from tb_branch where IsActived=1 order by  BranchName ";
		else if ($t=="p"){
			$sqlscript= "select ID as ID, ProductName as Name from tb_product where 1=1 and IsDeleted=0 and Type=1 order by ProductName ";
 
		}
		$result = $this->ExecuteData($sqlscript);
		
		 while($row = $result->FetchRow()){
				$ar[$row["Name"]] = $row["ID"];
		 }
		 
		return $ar;
	}
	function updateOrderStatus($id,$user, $uid ,$s){
			$sqlupdate=" Update tb_order_product  ";
			$sqlupdate.=" set Status=".$s ; 
			if ($s=='3'){
				$sqlupdate.=", ConfirmDate=Now() " ;
				$sqlupdate.=", ConfirmBy='".$user ."'" ;			
			}else if ($s=='2'){
				$sqlupdate.=", OrderDate=Now() ";
				$sqlupdate.=", OrderBy='".$user ."'";			
			}else if ($s=='4'){
				$sqlupdate.=", ClosedDate=Now() ";
				$sqlupdate.=", ClosedBy='".$user ."'";
			}
			$sqlupdate.=", UpdatedDate=Now() " ;
			$sqlupdate.=", UpdatedBy='".$user ."'"	;	
			$sqlupdate.=", UpdatedUserID='".$uid ."'"	;				
			$sqlupdate.=" Where ID=".$id;		
			
			$this->ExecuteData($sqlupdate);
	}
	function UpdateOrderProduct($id,$amt,$type){
		if ($type=='c'){ 
			//check from HO
			$sql=" update tb_order_product_detail set Amount_r =".$amt ." where ID=". $id;
		}else{
			//confirm from Branch
			$sql=" update tb_order_product_detail set Amount_c =".$amt ." where ID=". $id;
		}
		$this->ExecuteData($sql);
		
		echo $sql;
	}
 	function convertStock($pid){
		$sql = 'SELECT coalesce(Scale ,1) Scale
				FROM  tb_product
				Where ID ='.$pid.' and SubUnit <> 0';
				
		$result=$this->ExecuteData($sql);
		$count= $result->RecordCount();
		if ($count>0){
			$row=$result->FetchRow();
			return $row['Scale'];
		}else{
			return 1;
		}
	}
	function up_to_TempStock($bid,$pid,$amt){
		//global $HO_ID;
		$scale=$this->convertStock($pid);
		
			$sql=" update tb_stock_product "
		. " set Amount= Amount - ".($scale* $amt)
		. " , MainAmt=  ((Amount) - (Amount %".$scale ."))/".$scale 
		. " , UpdatedDate='".date( "Y/m/d H:i:s" )."'"
		. " Where BranchID=".$bid 
		. " And ProductID=".$pid;
				
		//$sql=" update tb_stock_product set Amount=Amount-(".$scale * $amt."), MainAmt=MainAmt-".$amt."  where BranchID=".$bid." and ProductID=".$pid;
		$this->ExecuteData($sql);
		return $sql;
	}
}
?>